package com.hongdee.insurance.order.service.impl;

import com.hongdee.insurance.car.service.ITInsuranceCarService;
import com.hongdee.insurance.order.dao.ITInsuranceOrderDao;
import com.hongdee.insurance.order.service.ITInsuranceOrderToCrmService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * @author 王帅
 * @className ${CLASS_NAME}
 * @type
 * @description
 * @path com.hongdee.insurance.order.service.impl
 * @create 2017/7/3 10:52
 **/
@Service
public class TInsuranceOrderToCrmServiceImpl implements ITInsuranceOrderToCrmService {

    @Autowired
    private ITInsuranceOrderDao orderDao;

    @Autowired
    private ITInsuranceCarService insuranceCarService;
    
    @Override
    public void insuranceOrderGoToCrmInsurance(String companyId,String orderId) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append("INSERT INTO t_crm_car_insurance " +
                " (id,create_time,create_user,update_time,update_user,base_type,car_series,commercial_company," +
                " commercialinsurancenum,company_id,compulsory_company,compulsory_end_date,compulsoryinsurancenum," +
                " compulsory_start_date,contractstartinsurance,contractstopinsurance,user_carid,plate_num,user_name," +
                " user_tel,vin,iscompulsoryinsurance,store_chanel,store_area,store_area_code,store_p_area,store_p_area_code," +
                " store_code,store_name,sale )" +
                " SELECT" +
                " tio.id,tio.create_time,tio.create_user,tio.update_time,tio.update_user," +
                " dict.label insure_type,tc.car_series," +
                " tic.insurance_company_name,sy.contract_no,tio.company_id,tic.insurance_company_name," +
                " (UNIX_TIMESTAMP(jq.stop_date))*1000,jq.contract_no,(UNIX_TIMESTAMP(jq.start_date))*1000," +
                " (UNIX_TIMESTAMP(sy.start_date))*1000,(UNIX_TIMESTAMP(sy.stop_date))*1000,tc.id," +
                " tc.plate_num,tio.insured,tc.contact_phone,tc.vin,tio.iscompulsoryinsurance,tio.store_chanel," +
                " tio.store_area,tio.store_area_code,tio.store_p_area,tio.store_p_area_code,tio.store_code,tio.store_name," +
                " tio.insurance_commissioner_name " +
                " FROM" +
                " T_Insurance_Order tio" +
                " LEFT JOIN t_insurance_car tc ON tio.car_id = tc.id" +
                " LEFT JOIN (SELECT * FROM t_insurance_order_item WHERE contract_type = 'jq' ) AS jq ON tio.id = jq.order_id" +
                " LEFT JOIN (SELECT * FROM t_insurance_order_item WHERE contract_type = 'sy' ) AS sy ON tio.id = sy.order_id" +
                " LEFT JOIN t_insurance_company tic ON tio.insurance_company_id = tic.id" +
                " LEFT JOIN t_bc_user u ON u.id = tio.create_user " +
                " LEFT JOIN (select * from t_bc_newdict where p_code='insuranceType') dict ON tio.base_type=dict.id "+
                " where not EXISTS(select * from t_crm_car_insurance where id=tio.id) and tio.company_id=? and tio.id=? and tio.insurance_type='0' ");
        this.orderDao.executeSQL(sql.toString(),companyId,orderId);
        insuranceCarGoToCrmCar(companyId);
    }

    @Override
    public void insuranceCarGoToCrmCar(String companyId) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append("insert into t_crm_car (id,create_time,create_user,update_time,update_user,owner_address,owner_identity,model,owner_name,owner_tel,owner_phone,owner_email, " +
                "guarantee_start_date,buy_date,plate_num,vin,company_id,brand,series,configure)  " +
                "select id,create_time,create_user,update_time,update_user,insured_address,insured_ID_number,car_model,contact_name,contact_phone,contact_phone,email, " +
                "UNIX_TIMESTAMP(DATE_FORMAT(first_record_date,'%Y-%m-%d'))*1000,UNIX_TIMESTAMP(DATE_FORMAT(first_record_date,'%Y-%m-%d'))*1000,plate_num,vin,company_id,car_brand,car_series,car_configure from t_insurance_car car  " +
                "where not EXISTS(select * from t_crm_car where id=car.id) and car.company_id=? and car.first_record_date is not null");
        this.orderDao.executeSQL(sql.toString(),companyId);
    }

    @Override
    public void updInsuranceOrderToCrmInsurance(String orderId, String companyId) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append("  update t_crm_car_insurance i" +
                " ,(SELECT" +
                " tio.id,tio.create_time,tio.create_user,tio.update_time,tio.update_user," +
                " dict.label insure_type,tc.car_series," +
                " tic.insurance_company_name,sy.contract_no sy_contract_no,tio.company_id," +
                " (UNIX_TIMESTAMP(jq.stop_date))*1000 jqStopDate,jq.contract_no jq_contract_no,(UNIX_TIMESTAMP(jq.start_date))*1000 jqStartDate," +
                " (UNIX_TIMESTAMP(sy.start_date))*1000 syStartDate,(UNIX_TIMESTAMP(sy.stop_date))*1000 syStopDate,tc.id userId," +
                " tc.plate_num,tio.insured,tc.contact_phone,tc.vin,tio.iscompulsoryinsurance " +
                " FROM" +
                " T_Insurance_Order tio" +
                " LEFT JOIN t_insurance_car tc ON tio.car_id = tc.id" +
                " LEFT JOIN (SELECT * FROM t_insurance_order_item WHERE contract_type = 'jq' ) AS jq ON tio.id = jq.order_id" +
                " LEFT JOIN (SELECT * FROM t_insurance_order_item WHERE contract_type = 'sy' ) AS sy ON tio.id = sy.order_id" +
                " LEFT JOIN t_insurance_company tic ON tio.insurance_company_id = tic.id" +
                " LEFT JOIN t_bc_user u ON u.id = tio.create_user " +
                " LEFT JOIN (select * from t_bc_newdict where p_code='insuranceType') dict ON tio.base_type=dict.id " +
                " where tio.id=? and tio.company_id=?) o " +
                " set i.base_type=o.insure_type,i.car_series=o.car_series,i.commercial_company=o.insurance_company_name," +
                " i.commercialinsurancenum=o.sy_contract_no,i.company_id=o.company_id,i.compulsory_company=o.insurance_company_name," +
                " i.compulsory_end_date=o.jqStopDate,i.compulsoryinsurancenum=o.jq_contract_no,i.compulsory_start_date=o.jqStartDate," +
                " i.contractstartinsurance=o.syStartDate,i.contractstopinsurance=o.syStopDate,i.user_carid=o.userId," +
                " i.plate_num=o.plate_num,i.user_name=insured,i.user_tel=o.contact_phone,i.iscompulsoryinsurance=o.iscompulsoryinsurance" +
                " where i.id=o.id ");
        this.orderDao.executeSQL(sql.toString(),orderId,companyId);
    }
}
